package cn.com.shopec.erp.common.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;

public abstract class ExcelBaseExport {

	private HSSFWorkbook workbook = null;
	private HSSFSheet sheet = null;
	private String fileName = null;
	private HttpServletResponse response = null;

	protected List<?> datas = null;
	protected List<String> titleColumns = null;
	protected HSSFCellStyle titleStyle = null;
	protected HSSFCellStyle bodyStyle = null;

	private int titleFontSize = 12;// 表头字号
	private int bodyFontSize = 12;// 正文字号
	private String titleBackColor = "C1FBEE";// 表头背景色
	private String bodyFontType = "Arial Unicode MS";// 正文字体
	private String titleFontType = "Arial Unicode MS";// 表头字体

	/**
	 * 构造方法
	 * @param data
	 * @param fileName
	 * @param response
	 */
	protected ExcelBaseExport(List<?> data, String fileName, HttpServletResponse response) {
		this.init(data, fileName, response);
	}

	/**
	 * 初始化导出需要的类成员变量
	 * @param data
	 * @param fileName
	 * @param response
	 */
	private void init(List<?> data, String fileName, HttpServletResponse response) {
		this.datas = data;
		this.fileName = fileName;
		this.response = response;
		this.workbook = new HSSFWorkbook();
		this.sheet = this.workbook.createSheet();
		this.titleColumns = setExcelTitleValue();
	}

	/**
	 * 将数据写入excel
	 * @throws IOException
	 */
	public void wirteExcel() throws IOException {
		OutputStream out = null;
		try {
			out = this.response.getOutputStream();
			this.fileName = fileName + ".xls";
			this.response.setContentType("application/vnd..ms-excel");
			this.response.setHeader("Content-Disposition",
					"attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
			setTitleStyle();
			setBodyStyle();
			setTitleValue();
			setBodyValue();
			this.workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			out.close();
		}
	}

	/**
	 * 导出表头
	 */
	private void setTitleValue() {
		Row row = this.sheet.createRow(0);
		for (int i = 0; i < this.titleColumns.size(); i++) {
			this.sheet.setColumnWidth((short) i, (short) 256 * 20);
			Cell cell = row.createCell(i);
			cell.setCellValue(this.titleColumns.get(i));
			cell.setCellStyle(this.titleStyle);
		}
	}

	/**
	 * 导出表体数据
	 */
	private void setBodyValue() {
		if (this.datas != null && this.datas.size() > 0) {
			for (int i = 0; i < datas.size(); i++) {
				Object data = datas.get(i);
				setColumnMapping(data, sheet.createRow(i+1));
			}
		}
	}

	/**
	 * 配置数据与excel列映射关系
	 * @param data
	 * @param row
	 * @return
	 */
	protected abstract Row setColumnMapping(Object data, Row row);

	/**
	 * 设置表头
	 * @return
	 */
	protected abstract List<String> setExcelTitleValue();

	/**
	 * 设置表头样式
	 */
	private void setTitleStyle() {
		this.titleStyle = this.workbook.createCellStyle();
		this.titleStyle = (HSSFCellStyle) setFontAndBorder(this.titleStyle, this.titleFontType,
				(short) this.titleFontSize);
		this.titleStyle = (HSSFCellStyle) setColor(this.titleStyle, this.titleBackColor, (short) 10);
	}

	/**
	 * 设置表体样式
	 */
	private void setBodyStyle() {
		this.bodyStyle = this.workbook.createCellStyle();
		this.bodyStyle = (HSSFCellStyle) setFontAndBorder(this.bodyStyle, this.bodyFontType, (short) this.bodyFontSize);
	}

	/**
	 * 设置列字体样式与边框
	 * 
	 * @param style
	 * @param fontName
	 * @param size
	 * @return
	 */
	private CellStyle setFontAndBorder(CellStyle style, String fontName, short size) {
		HSSFFont font = this.workbook.createFont();
		font.setFontHeightInPoints(size);
		font.setFontName(fontName);
		style.setFont(font);
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		return style;
	}

	/**
	 * 设置列颜色
	 * 
	 * @param cellStyle
	 * @param color
	 * @param index
	 * @return
	 */
	private CellStyle setColor(CellStyle cellStyle, String color, short index) {
		int r = Integer.parseInt((color.substring(0, 2)), 16);
		int g = Integer.parseInt((color.substring(2, 4)), 16);
		int b = Integer.parseInt((color.substring(4, 6)), 16);
		HSSFPalette palette = this.workbook.getCustomPalette();
		palette.setColorAtIndex((short) index, (byte) r, (byte) g, (byte) b);
		cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cellStyle.setFillForegroundColor(index);
		return cellStyle;
	}

	public void setTitleFontType(String titleFontType) {
		this.titleFontType = titleFontType;
	}

	public void setTitleFontSize(int titleFontSize) {
		this.titleFontSize = titleFontSize;
	}

	public void setBodyFontType(String bodyFontType) {
		this.bodyFontType = bodyFontType;
	}

	public void setBodyFontSize(int bodyFontSize) {
		this.bodyFontSize = bodyFontSize;
	}

	public void setTitleBackColor(String titleBackColor) {
		this.titleBackColor = titleBackColor;
	}

}
